﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Entity;
using System.Data;
using System.Data.OleDb;

namespace web.admin.Academy
{
    public partial class ACA_Edit : System.Web.UI.Page
    {
       
        protected void Page_Load(object sender, EventArgs e)
        {

        }

       

        /// <summary>
        /// 批量上传事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btn_up_Click(object sender, EventArgs e)
        {
            BLL.AcademyService academyservice = new BLL.AcademyService();
            BLL.LocalService localservice = new BLL.LocalService();
             //判断是否选中文件
            if (ExcelUpLoad.HasFile)
            {
                //得到文件类型
                string fileClass = System.IO.Path.GetExtension(ExcelUpLoad.FileName).ToString();
                if (fileClass == ".xls" || fileClass == ".xlsx")
                {
                    string SavaPath = MapPath("../../Upload").ToString();
                    //如果不存在此路径，则创建
                    if (!System.IO.Directory.Exists(SavaPath))
                    {
                        System.IO.Directory.CreateDirectory(SavaPath);
                    }
                    string name = ExcelUpLoad.FileName;
                    DateTime dtime = DateTime.Now;
                    string newname = dtime.ToString("yyyyMMddHHmmssff") + fileClass;
                    //保存完整路径
                    SavaPath = SavaPath + "\\" + newname;
                    //保存文件
                    ExcelUpLoad.SaveAs(SavaPath);
                    DataTable dt = ExcelToDb(SavaPath, "Sheet1");
                    BoAcademy Model = new BoAcademy();
                    int count = 0;
                    //excel的格式要求，班级编码,班级名称,班级简称,班级英文名,所属学院,学生人数,入学日期,专业,学制类型,是否显示,备注,校区
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (!string.IsNullOrEmpty(dr["院系号"].ToString()))
                        {
                            if (academyservice.getAcademyByCode(dr["院系号"].ToString())!=null)
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该学院已经存在')</script>");
                                return;
                            }
                            else
                            {
                                Model.ACA_CODE = dr["院系号"].ToString();
                            }
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,院系号不允许为空,请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["院系名"].ToString()))
                            Model.ACA_NAME = dr["院系名"].ToString();
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,学院名称不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["院系简称"].ToString()))
                            Model.ACA_SHORT = dr["院系简称"].ToString();
                        else
                        {
                            Model.ACA_SHORT = "";
                        }
                        if (!string.IsNullOrEmpty(dr["英文院系名"].ToString()))
                            Model.ACA_ENNAME = dr["英文院系名"].ToString();
                        else
                        {
                            Model.ACA_ENNAME = "";
                        }
                        if (!string.IsNullOrEmpty(dr["是否教学单位"].ToString()))
                        {
                            Model.ACA_IsTeach = dr["是否教学单位"].ToString();
                        }
                        else
                        {
                            Model.ACA_IsTeach = "是";
                        }
                        if (!string.IsNullOrEmpty(dr["备注"].ToString()))
                            Model.ACA_REMARK = dr["备注"].ToString();
                        else
                        {
                            Model.ACA_REMARK = " ";
                        }
                        if (!string.IsNullOrEmpty(dr["所在校区"].ToString()))
                        {
                            BoLocal localModel = localservice.GetLocalByName(dr["所在校区"].ToString());
                            if (localModel != null)
                                Model.ACA_LOCAL = localModel.LOC_ID;
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,不存在该校区')</script>");
                                return;
                            }
                        }
                        else
                            Model.ACA_LOCAL = "";

                        academyservice.CreateAcademy(Model);
                        count++;
                    }
                    Response.Write("<script>alert('添加成功，共添加" + count + "条数据')</script>");

                }
                else
                    //当然这个工作可以在前台做，可以用javascript来做，给upfileload添加一个事件即可
                    Response.Write("<script>alert('请选择正确的excel表格')</script>");
            }
        }
        /// <summary>
        /// 将excel表格用access数据库打开
        /// </summary>
        /// <param name="ExcelFileName">文件路径</param>
        /// <param name="ExcelSheet">工作表名称</param>
        /// <returns>返回excel中表格数据</returns>
        protected DataTable ExcelToDb(string ExcelFileName, string ExcelSheet)
        {
            //犹如sqlserver中的sqlconnection
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            //犹如sqlstring，查询语句
            string strExcel = string.Format("select * from [{0}$]", ExcelSheet);
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection(strCon))
            {
                OleDbDataAdapter odda = new OleDbDataAdapter(strExcel, con);
                odda.Fill(dt);
                return dt;
            }
        }
    }
}